<!DOCTYPE html><html><head><title>MySQL学习笔记（Day008：数据类型）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-99" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-100" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day008数据类型">MySQL学习笔记（Day008：数据类型）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day008数据类型">MySQL学习笔记（Day008：数据类型）</a><ul>
<li><a href="#一-int类型">一. INT类型</a><ul>
<li><a href="#1-int类型的分类">1. INT类型的分类</a></li>
<li><a href="#2-int类型的使用">2. INT类型的使用</a></li>
<li><a href="#3-intn">3. INT(N)</a></li>
<li><a href="#4-autoincrement">4. AUTO_INCREMENT</a></li>
</ul>
</li>
<li><a href="#二-数字类型">二. 数字类型</a><ul>
<li><a href="#1-数字类型的分类">1. 数字类型的分类</a></li>
</ul>
</li>
<li><a href="#三-字符串类型">三. 字符串类型</a><ul>
<li><a href="#1-字符串类型介绍">1. 字符串类型介绍</a></li>
<li><a href="#2-n和字符集">2. N和字符集</a></li>
<li><a href="#3blob和text">3.BLOB和TEXT</a></li>
</ul>
</li>
<li><a href="#四-字符集">四. 字符集</a><ul>
<li><a href="#1-常见的字符集">1. 常见的字符集</a></li>
<li><a href="#2-collation">2. collation</a></li>
</ul>
</li>
<li><a href="#五-集合类型">五. 集合类型</a><ul>
<li><a href="#1-集合类型的排序">1. 集合类型的排序</a></li>
<li><a href="#2-集合类型的排序">2. 集合类型的排序</a></li>
</ul>
</li>
<li><a href="#六-日期类型">六. 日期类型</a><ul>
<li><a href="#1-timestamp和datetime">1. TIMESTAMP和DATETIME</a></li>
<li><a href="#2-微秒">2. 微秒</a></li>
<li><a href="#3-时间函数">3. 时间函数</a></li>
<li><a href="#4-字段更新时间">4. 字段更新时间</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-101" class="wmd-preview-section preview-content">

<h2 id="一-int类型">一. INT类型</h2>

</div><div id="wmd-preview-section-102" class="wmd-preview-section preview-content">

<h3 id="1-int类型的分类">1. INT类型的分类</h3>

<ul><li><p><strong>TINYINT</strong></p>

<ul>
<li>存储空间 ： 1 字节</li>
<li>取值范围  <br>
<ul>
<li>有符号(signed) ：  [-128, 127]</li>
<li>无符号(unsigned) ：[0, 255]</li></ul></li></ul></li>
<li><p><strong>SMALLINT</strong></p>

<ul>
<li>存储空间 ： 2 字节</li>
<li>取值范围  <br>
<ul>
<li>有符号(signed) ：  [-32768, 32767]</li>
<li>无符号(unsigned) ：[0, 65535]</li></ul></li></ul></li>
<li><p><strong>MEDIUMINT</strong></p>

<ul>
<li>存储空间 ： 3 字节</li>
<li>取值范围  <br>
<ul>
<li>有符号(signed) ：  [-8388608, 8388607]</li>
<li>无符号(unsigned) ：[0, 16777215]</li></ul></li></ul></li>
<li><p><strong>INT</strong></p>

<ul>
<li>存储空间 ： 4 字节</li>
<li>取值范围  <br>
<ul>
<li>有符号(signed) ：  [-2147483648, 2147483647]</li>
<li>无符号(unsigned) ：[0, 4294967295]</li></ul></li></ul></li>
<li><p><strong>BIGINT</strong></p>

<ul>
<li>存储空间 ： 8 字节</li>
<li>取值范围  <br>
<ul>
<li>有符号(signed) ：  [-9223372036854775808, 9223372036854775807]</li>
<li>无符号(unsigned) ：[0, 18446744073709551615]</li></ul></li></ul></li>
</ul>

</div><div id="wmd-preview-section-103" class="wmd-preview-section preview-content">

<h3 id="2-int类型的使用">2. INT类型的使用</h3>

<ul><li><p><strong>自增长ID</strong></p>

<ul>
<li><code>推荐</code>使用<code>BIGINT</code>，而不是INT；</li></ul></li>
<li><p><strong>unsigned or signed</strong></p>

<ul>
<li>根据实际情况使用，一般情况下推荐<code>默认</code>的<code>sigend</code></li>
<li>unsigned 的注意事项</li></ul></li>
</ul>

</div><div id="wmd-preview-section-104" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_unsigned(a <span class="hljs-built_in">int</span> unsigned, b <span class="hljs-built_in">int</span> unsigned);</span><br>Query OK, 0 rows affected (0.14 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_unsigned <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>, <span class="hljs-number">2</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a - b <span class="hljs-keyword">from</span> test_unsigned;</span><br>ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`burn_test`.`test_unsigned`.`a` - `burn_test`.`test_unsigned`.`b`)'<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> b - a <span class="hljs-keyword">from</span> test_unsigned;</span>   <br>+<span class="hljs-comment">-------+</span><br>| b - a |<br>+<span class="hljs-comment">-------+</span><br>|     1 |<br>+<span class="hljs-comment">-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">set</span> sql_mode = <span class="hljs-string">'no_unsigned_subtraction'</span>;</span> <span class="hljs-comment">-- 这样就可以得到负数</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a - b <span class="hljs-keyword">from</span> test_unsigned;</span><br>+<span class="hljs-comment">-------+</span><br>| a - b |<br>+<span class="hljs-comment">-------+</span><br>|    -1 |<br>+<span class="hljs-comment">-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec) </span><br></code></pre>

<blockquote>
  <p>一般情况下使用<code>int</code>时，推荐有符号数<code>(signed)</code>， 使用无符号数只是比原来多一倍的取值，数量级上没有改变。</p>
  
  <p>如果需要取值范围很大，直接选择用<code>BIGINT</code></p>
</blockquote>

</div><div id="wmd-preview-section-105" class="wmd-preview-section preview-content">

<h3 id="3-intn">3. INT(N)</h3>

</div><div id="wmd-preview-section-106" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span>  test_unsigned;</span><br>+<span class="hljs-comment">---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+</span><br>| Table         | <span class="hljs-operator"><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>                                                                                                                                    |<br>+<span class="hljs-comment">---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+</span><br>| test_unsigned | <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`test_unsigned`</span> (<br>  <span class="hljs-string">`a`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">10</span>) unsigned <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, <br>  <span class="hljs-string">`b`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">10</span>) unsigned <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span><br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4 |<br>+<span class="hljs-comment">---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<ul><li><strong>int(N) 和 zerofill</strong> <br>
<ul>
<li>int(<strong>N</strong>)中的<code>N</code>是显示宽度，<code>不表示</code>存储的数字的<code>长度</code>的上限。</li>
<li><code>zerofill</code>表示当存储的数字<code>长度 &lt; N</code>时，用<code>数字0</code>填充左边，直至补满长度<code>N</code></li>
<li>当存储数字的长度<code>超过N时</code>，按照<code>实际存储</code>的数字显示</li></ul></li>
</ul>

</div><div id="wmd-preview-section-107" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span>  <span class="hljs-keyword">table</span>  test_int_n(a <span class="hljs-built_in">int</span>(<span class="hljs-number">3</span>) zerofill);</span>  <span class="hljs-comment">-- 显示宽度N=3</span><br>Query OK, 0 rows affected (0.11 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_int_n <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>);</span><br>Query OK, 1 row affected (0.04 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_int_n;</span><br>+<span class="hljs-comment">------+</span><br>| a    |<br>+<span class="hljs-comment">------+</span><br>|  001 |   <span class="hljs-comment">-- 不满 N=3时，左边用0填充</span><br>+<span class="hljs-comment">------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_int_n <span class="hljs-keyword">values</span>(<span class="hljs-number">1111</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_int_n;</span><br>+<span class="hljs-comment">------+</span><br>| a    |<br>+<span class="hljs-comment">------+</span><br>|  001 |<br>| 1111 |  <span class="hljs-comment">-- 超过N=3的长度时，是什么数字，显示什么数字</span><br>+<span class="hljs-comment">------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> a, <span class="hljs-keyword">HEX</span>(a) <span class="hljs-keyword">from</span> test_int_n\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>     a: <span class="hljs-number">001</span><br><span class="hljs-keyword">HEX</span>(a): <span class="hljs-number">1</span>    <span class="hljs-comment">-- 实际存储的还是1</span><br>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************<br>     a: <span class="hljs-number">1111</span><br><span class="hljs-keyword">HEX</span>(a): <span class="hljs-number">457</span>  <span class="hljs-comment">-- 1111对应的16进制就是457</span><br><span class="hljs-number">2</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p>int(N)中的<code>N</code>和<code>zerofill</code>配合才有意义，且仅仅是显示的时候才有意义，和实际存储没有关系，不会去截取数字的长度。</p>
</blockquote>

</div><div id="wmd-preview-section-108" class="wmd-preview-section preview-content">

<h3 id="4-autoincrement">4. AUTO_INCREMENT</h3>

<ul><li>自增</li>
<li>每张表一个</li>
<li>必须是索引的一部分</li>
</ul>

</div><div id="wmd-preview-section-109" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_auto_increment(a <span class="hljs-built_in">int</span> auto_increment);</span><br>ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key<br><span class="hljs-comment">-- 没有指定为key，报错了</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_auto_increment(a <span class="hljs-built_in">int</span> auto_increment <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>);</span>  <span class="hljs-comment">-- 指定为key后有效</span><br>Query OK, 0 rows affected (0.11 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_auto_increment <span class="hljs-keyword">values</span>(<span class="hljs-literal">NULL</span>);</span>  <span class="hljs-comment">-- 插入NULL值</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_auto_increment;</span><br>+<span class="hljs-comment">---+</span><br>| a |<br>+<span class="hljs-comment">---+</span><br>| 1 |  <span class="hljs-comment">-- 插入NULL值，便可以让其自增，且默认从1开始</span><br>+<span class="hljs-comment">---+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_auto_increment <span class="hljs-keyword">values</span>(<span class="hljs-number">0</span>);</span>  <span class="hljs-comment">-- 插入 0</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_auto_increment;</span><br>+<span class="hljs-comment">---+</span><br>| a |<br>+<span class="hljs-comment">---+</span><br>| 1 |<br>| 2 |  <span class="hljs-comment">-- 插入 0 ，自增长为2</span><br>+<span class="hljs-comment">---+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_auto_increment <span class="hljs-keyword">values</span>(-<span class="hljs-number">1</span>);</span>  <span class="hljs-comment">-- 插入 -1</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_auto_increment;</span><br>+<span class="hljs-comment">----+</span><br>| a  |<br>+<span class="hljs-comment">----+</span><br>| -1 |   <span class="hljs-comment">-- 刚刚插入的-1</span><br>|  1 |<br>|  2 |<br>+<span class="hljs-comment">----+</span><br>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_auto_increment <span class="hljs-keyword">values</span>(<span class="hljs-literal">NULL</span>);</span>  <span class="hljs-comment">-- 继续插入NULL</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_auto_increment;</span><br>+<span class="hljs-comment">----+</span><br>| a  |<br>+<span class="hljs-comment">----+</span><br>| -1 |<br>|  1 |<br>|  2 |<br>|  3 |  <span class="hljs-comment">-- 刚刚插入NULL， 自增为3</span><br>+<span class="hljs-comment">----+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_auto_increment <span class="hljs-keyword">values</span>(<span class="hljs-string">'0'</span>);</span> <span class="hljs-comment">-- 插入字符0</span><br>Query OK, 1 row affected (0.04 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_auto_increment;</span><br>+<span class="hljs-comment">----+</span><br>| a  |<br>+<span class="hljs-comment">----+</span><br>| -1 |<br>|  1 |<br>|  2 |<br>|  3 |<br>|  4 |  <span class="hljs-comment">-- 插入字符'0' 后， 自增长为4</span><br>+<span class="hljs-comment">----+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">update</span> test_auto_increment <span class="hljs-keyword">set</span> a = <span class="hljs-number">0</span> <span class="hljs-keyword">where</span> a = -<span class="hljs-number">1</span>;</span>  <span class="hljs-comment">-- 更新为0</span><br>Query OK, 1 row affected (0.03 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_auto_increment;</span><br>+<span class="hljs-comment">---+</span><br>| a |<br>+<span class="hljs-comment">---+</span><br>| 0 |  <span class="hljs-comment">-- 原来的 -1 更新为0</span><br>| 1 |<br>| 2 |<br>| 3 |<br>| 4 |<br>+<span class="hljs-comment">---+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">--  数字 0 这个值比较特殊， 插入0和插入NULL的效果是一样的，都是代表自增</span><br><span class="hljs-comment">--</span><br><br><span class="hljs-comment">-----</span><br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_auto_increment <span class="hljs-keyword">values</span>(<span class="hljs-literal">NULL</span>), (<span class="hljs-number">100</span>), (<span class="hljs-literal">NULL</span>);</span> <br>Query OK, 3 rows affected (0.02 sec)<br>Records: 3  Duplicates: 0  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_auto_increment;</span><br>+<span class="hljs-comment">-----+</span><br>| a   |<br>+<span class="hljs-comment">-----+</span><br>|   0 |<br>|   1 |<br>|   2 |<br>|   3 |<br>|   4 |<br>|   5 | <span class="hljs-comment">-- 第一个NULL</span><br>| 100 | <span class="hljs-comment">-- 100</span><br>| 101 | <span class="hljs-comment">-- 第二个NULL, 按当前最大的值 +１来设置，之前是100，所以这里101</span><br>+<span class="hljs-comment">-----+</span><br>8 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_auto_increment <span class="hljs-keyword">values</span>(<span class="hljs-number">99</span>);</span> <span class="hljs-comment">-- 插入99</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_auto_increment;</span><br>+<span class="hljs-comment">-----+</span><br>| a   |<br>+<span class="hljs-comment">-----+</span><br>|   0 |<br>|   1 |<br>|   2 |<br>|   3 |<br>|   4 |<br>|   5 |<br>|  99 |  <span class="hljs-comment">-- 刚刚插入的 99</span><br>| 100 |<br>| 101 |<br>+<span class="hljs-comment">-----+</span><br>9 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p><code>AUTO_INCREMENT</code>是实例启动时，取当前表的最大值，然后 +1 即为下次自增的值。<code>（MAX + 1)</code></p>
  
  <p><strong>TIPS:</strong> <br>
  <code>insert into tablename select NULL;</code> 等价与 <code>insert into tablename values (NULL);</code></p>
</blockquote>

<hr>

</div><div id="wmd-preview-section-110" class="wmd-preview-section preview-content">

<h2 id="二-数字类型">二. 数字类型</h2>

</div><div id="wmd-preview-section-111" class="wmd-preview-section preview-content">

<h3 id="1-数字类型的分类">1. 数字类型的分类</h3>

<ul><li><p>单精度类型：FLOAT</p>

<ul>
<li>存储空间：4 字节</li>
<li>精确性：低</li></ul></li>
<li><p>双精度类型：DOUBLE</p>

<ul>
<li>占用空间：8 字节</li>
<li>精确性：低，比FLOAT高</li></ul></li>
<li><p>高精度类型：DECIMAL</p>

<ul>
<li>占用空间：变长</li>
<li>精确性：非常高</li></ul></li>
</ul>

<p><strong>注意：财务系统<code>必须使用DECIMAL</code></strong></p>

</div><div id="wmd-preview-section-112" class="wmd-preview-section preview-content">

<hr>

</div><div id="wmd-preview-section-113" class="wmd-preview-section preview-content">

<h2 id="三-字符串类型">三. 字符串类型</h2>

</div><div id="wmd-preview-section-114" class="wmd-preview-section preview-content">

<h3 id="1-字符串类型介绍">1. 字符串类型介绍</h3>

<table>
<thead>
<tr>
  <th align="center">类型</th>
  <th align="center">说明</th>
  <th align="center">N的含义</th>
  <th align="center">是否有字符集</th>
  <th align="center">最大长度</th>
</tr>
</thead>
<tbody><tr>
  <td align="center"><code>CHAR(N)</code></td>
  <td align="center">定长字符</td>
  <td align="center">字符</td>
  <td align="center">是</td>
  <td align="center">255</td>
</tr>
<tr>
  <td align="center"><code>VARCHAR(N)</code></td>
  <td align="center">变长字符</td>
  <td align="center">字符</td>
  <td align="center">是</td>
  <td align="center">16384</td>
</tr>
<tr>
  <td align="center">BINARY(N)</td>
  <td align="center">定长二进制字节</td>
  <td align="center">字节</td>
  <td align="center">否</td>
  <td align="center">255</td>
</tr>
<tr>
  <td align="center">VARBINARY(N)</td>
  <td align="center">变长二进制字节</td>
  <td align="center">字节</td>
  <td align="center">否</td>
  <td align="center">16384</td>
</tr>
<tr>
  <td align="center">TINYBLOB(N)</td>
  <td align="center">二进制大对象</td>
  <td align="center">字节</td>
  <td align="center">否</td>
  <td align="center">256</td>
</tr>
<tr>
  <td align="center">BLOB(N)</td>
  <td align="center">二进制大对象</td>
  <td align="center">字节</td>
  <td align="center">否</td>
  <td align="center">16K</td>
</tr>
<tr>
  <td align="center">MEDIUMBLOB(N)</td>
  <td align="center">二进制大对象</td>
  <td align="center">字节</td>
  <td align="center">否</td>
  <td align="center">16M</td>
</tr>
<tr>
  <td align="center">LONGBLOB(N)</td>
  <td align="center">二进制大对象</td>
  <td align="center">字节</td>
  <td align="center">否</td>
  <td align="center">4G</td>
</tr>
<tr>
  <td align="center">TINYTEXT(N)</td>
  <td align="center">大对象</td>
  <td align="center">字节</td>
  <td align="center">是</td>
  <td align="center">256</td>
</tr>
<tr>
  <td align="center">TEXT(N)</td>
  <td align="center">大对象</td>
  <td align="center">字节</td>
  <td align="center">是</td>
  <td align="center">16K</td>
</tr>
<tr>
  <td align="center">MEDIUMTEXT(N)</td>
  <td align="center">大对象</td>
  <td align="center">字节</td>
  <td align="center">是</td>
  <td align="center">16M</td>
</tr>
<tr>
  <td align="center">LONGTEXT(N)</td>
  <td align="center">大对象</td>
  <td align="center">字节</td>
  <td align="center">是</td>
  <td align="center">4G</td>
</tr>
</tbody></table>


</div><div id="wmd-preview-section-115" class="wmd-preview-section preview-content">

<h3 id="2-n和字符集">2. N和字符集</h3>

<ul><li><strong>char(N)</strong> <br>
<ul>
<li>假设当前table的字符集的<code>最大长度</code>为<code>W</code>, 则<code>char(N)</code>的最大存储空间为 <code>(N X W)Byte</code>;假设使用<code>UTF-8</code>，则char(10)可以最小存储10个字节的字符，最大存储30个字节的字符，其实是另一种意义上的<code>varchar</code></li>
<li>当存储的字符数<code>小于N</code>时，尾部使用<code>空格</code>填充，并且填充最小字节的空格</li></ul></li>
</ul>

</div><div id="wmd-preview-section-116" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_char(a <span class="hljs-built_in">char</span>(<span class="hljs-number">10</span>));</span><br>Query OK, 0 rows affected (0.12 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_char;</span><br>+<span class="hljs-comment">-----------+------------------------------------------------------------------------------------------------+</span><br>| Table     | <span class="hljs-operator"><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>                                                                                   |<br>+<span class="hljs-comment">-----------+------------------------------------------------------------------------------------------------+</span><br>| test_char | <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`test_char`</span> (<br>  <span class="hljs-string">`a`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span><br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4 |<br>+<span class="hljs-comment">-----------+------------------------------------------------------------------------------------------------+</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_char <span class="hljs-keyword">values</span>(<span class="hljs-string">'abc'</span>);</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_char <span class="hljs-keyword">values</span>(<span class="hljs-string">'你好吗'</span>);</span><br>Query OK, 1 row affected (0.05 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_char <span class="hljs-keyword">values</span>(<span class="hljs-string">'大家好ab'</span>);</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_char <span class="hljs-keyword">values</span>(<span class="hljs-string">'大家ab好'</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_char <span class="hljs-keyword">values</span>(<span class="hljs-string">'大家ab好吗'</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a, length(a) <span class="hljs-keyword">from</span> test_char;</span><br>+<span class="hljs-comment">----------------+-----------+</span><br>| a              | length(a) |<br>+<span class="hljs-comment">----------------+-----------+</span><br>| abc            |         3 |<br>| 你好吗         |         9 |<br>| 大家好ab       |        11 |<br>| 大家ab好       |        11 |<br>| 大家ab好吗     |        14 |<br>+<span class="hljs-comment">----------------+-----------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> a, <span class="hljs-keyword">hex</span>(a) <span class="hljs-keyword">from</span> test_char;</span><br>+<span class="hljs-comment">----------------+------------------------------+</span><br>| a              | hex(a)                       |<br>+<span class="hljs-comment">----------------+------------------------------+</span><br>| abc            | 616263                       |    <span class="hljs-comment">-- 注意这里，以及下面的16进制值，一会可以对比</span><br>| 你好吗         | E4BDA0E5A5BDE59097           |<br>| 大家好ab       | E5A4A7E5AEB6E5A5BD6162       |<br>| 大家ab好       | E5A4A7E5AEB66162E5A5BD       |<br>| 大家ab好吗     | E5A4A7E5AEB66162E5A5BDE59097 |<br>+<span class="hljs-comment">----------------+------------------------------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">hex</span>(<span class="hljs-string">' '</span>);</span><br>+<span class="hljs-comment">----------+</span><br>| hex(' ') |<br>+<span class="hljs-comment">----------+</span><br>| 20       |   <span class="hljs-comment">-- 注意 空格 空格对应的16进制数字是 20</span><br>+<span class="hljs-comment">----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<p><strong><code>test_char</code>表实际二进制存储文件</strong></p>

</div><div id="wmd-preview-section-117" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">-- </span><br><span class="hljs-comment">-- shell&gt; hexdump -C test_char.idb</span><br><span class="hljs-comment">--</span><br><br><span class="hljs-comment">-- 1:abc</span><br><span class="hljs-comment">-- 2:你好吗</span><br><span class="hljs-comment">-- 3:大家好ab</span><br><span class="hljs-comment">-- 4:大家ab好</span><br><span class="hljs-comment">-- 5:大家ab好吗</span><br><br><span class="hljs-comment">-- ---省略---</span><br>00006070  73 75 70 72 65 6d 75 6d  0a 00 00 00 10 00 24 00  |supremum......$.|<br>00006080  00 00 00 02 03 00 00 00  00 1f 33 a8 00 00 00 26  |..........3....&amp;|<br>00006090  01 10 61 62 63 20 20 20  20 20 20 20 0a 00 00 00  |..abc       ....| <span class="hljs-comment">-- 1:后面补了7个空格</span><br>000060a0  18 00 24 00 00 00 00 02  04 00 00 00 00 1f 34 a9  |..$...........4.|<br>000060b0  00 00 00 25 01 10 e4 bd  a0 e5 a5 bd e5 90 97 20  |...%........... | <span class="hljs-comment">-- 2:补充了1个空格</span><br>000060c0  0b 00 00 00 20 00 25 00  00 00 00 02 05 00 00 00  |.... .%.........|<br>000060d0  00 1f 39 ac 00 00 00 26  01 10 e5 a4 a7 e5 ae b6  |..9....&amp;........| <span class="hljs-comment">-- 3:没有补充空格</span><br>000060e0  e5 a5 bd 61 62 0b 00 00  00 28 00 25 00 00 00 00  |...ab....(.%....|  <span class="hljs-comment">-- </span><br>000060f0  02 06 00 00 00 00 1f 3a  ad 00 00 00 28 01 10 e5  |.......:....(...| <span class="hljs-comment">--</span><br>00006100  a4 a7 e5 ae b6 61 62 e5  a5 bd 0e 00 00 00 30 ff  |.....ab.......0.|  <span class="hljs-comment">-- 4：没有补充空格</span><br>00006110  5f 00 00 00 00 02 07 00  00 00 00 1f 3f b0 00 00  |_...........?...|<br>00006120  00 29 01 10 e5 a4 a7 e5  ae b6 61 62 e5 a5 bd e5  |.)........ab....|<span class="hljs-comment">--</span><br>00006130  90 97 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................| <span class="hljs-comment">-- 5：没有补充空格</span><br>00006140  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|<br><span class="hljs-comment">-- ---省略---</span><br></code></pre>

<ul><li><strong>varchar(N)</strong></li>
</ul>

</div><div id="wmd-preview-section-188" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_varchar(a <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>));</span><br>Query OK, 0 rows affected (0.12 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_varchar;</span><br>+<span class="hljs-comment">--------------+------------------------------------------------------------------------------------------------------+</span><br>| Table        | <span class="hljs-operator"><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>                                                                                         |<br>+<span class="hljs-comment">--------------+------------------------------------------------------------------------------------------------------+</span><br>| test_varchar | <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`test_varchar`</span> (<br>  <span class="hljs-string">`a`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span><br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4 |<br>+<span class="hljs-comment">--------------+------------------------------------------------------------------------------------------------------+</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_varchar <span class="hljs-keyword">values</span>(<span class="hljs-string">'abc'</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_varchar <span class="hljs-keyword">values</span>(<span class="hljs-string">'你好吗'</span>);</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_varchar <span class="hljs-keyword">values</span>(<span class="hljs-string">'大家好ab'</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_varchar <span class="hljs-keyword">values</span>(<span class="hljs-string">'大家ab好'</span>);</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_varchar <span class="hljs-keyword">values</span>(<span class="hljs-string">'大家ab好吗'</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a, <span class="hljs-keyword">hex</span>(a) <span class="hljs-keyword">from</span> test_varchar;</span><br>+<span class="hljs-comment">----------------+------------------------------+</span><br>| a              | hex(a)                       |<br>+<span class="hljs-comment">----------------+------------------------------+</span><br>| abc            | 616263                       |<br>| 你好吗         | E4BDA0E5A5BDE59097           |<br>| 大家好ab       | E5A4A7E5AEB6E5A5BD6162       |<br>| 大家ab好       | E5A4A7E5AEB66162E5A5BD       |<br>| 大家ab好吗     | E5A4A7E5AEB66162E5A5BDE59097 |<br>+<span class="hljs-comment">----------------+------------------------------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> a, length(a) <span class="hljs-keyword">from</span> test_varchar;</span><br>+<span class="hljs-comment">----------------+-----------+</span><br>| a              | length(a) |<br>+<span class="hljs-comment">----------------+-----------+</span><br>| abc            |         3 |<br>| 你好吗         |         9 |<br>| 大家好ab       |        11 |<br>| 大家ab好       |        11 |<br>| 大家ab好吗     |        14 |<br>+<span class="hljs-comment">----------------+-----------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<p><strong><code>test_varchar</code>表实际二进制存储文件</strong></p></div><div id="wmd-preview-section-119" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">-- </span><br><span class="hljs-comment">-- shell&gt; hexdump -C test_char.idb</span><br><span class="hljs-comment">--</span><br><br><span class="hljs-comment">-- 1:abc</span><br><span class="hljs-comment">-- 2:你好吗</span><br><span class="hljs-comment">-- 3:大家好ab</span><br><span class="hljs-comment">-- 4:大家ab好</span><br><span class="hljs-comment">-- 5:大家ab好吗</span><br><br><span class="hljs-comment">-- 和char一样观察，都没有进行空格的填充</span><br><br>00006070  73 75 70 72 65 6d 75 6d  03 00 00 00 10 00 1d 00  |supremum........|<br>00006080  00 00 00 02 08 00 00 00  00 1f 44 b5 00 00 00 29  |..........D....)|<br>00006090  01 10 61 62 63 09 00 00  00 18 00 23 00 00 00 00  |..abc......#....| <br>000060a0  02 09 00 00 00 00 1f 45  b6 00 00 00 2b 01 10 e4  |.......E....+...|<br>000060b0  bd a0 e5 a5 bd e5 90 97  0b 00 00 00 20 00 25 00  |............ .%.|<br>000060c0  00 00 00 02 0a 00 00 00  00 1f 4a b9 00 00 00 2c  |..........J....,|<br>000060d0  01 10 e5 a4 a7 e5 ae b6  e5 a5 bd 61 62 0b 00 00  |...........ab...|<br>000060e0  00 28 00 25 00 00 00 00  02 0b 00 00 00 00 1f 4b  |.(.%...........K|<br>000060f0  ba 00 00 00 2c 01 10 e5  a4 a7 e5 ae b6 61 62 e5  |....,........ab.|<br>00006100  a5 bd 0e 00 00 00 30 ff  67 00 00 00 00 02 0c 00  |......0.g.......|<br>00006110  00 00 00 1f 50 bd 00 00  00 2d 01 10 e5 a4 a7 e5  |....P....-......|<br>00006120  ae b6 61 62 e5 a5 bd e5  90 97 00 00 00 00 00 00  |..ab............|<br>00006130  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|<br></code></pre>

<ul><li><strong>插入数据尾部带空格</strong></li>
</ul>

</div><div id="wmd-preview-section-120" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_char <span class="hljs-keyword">values</span>(<span class="hljs-string">'好好好   '</span>);</span>  <span class="hljs-comment">-- 后面有3个空格</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_varchar <span class="hljs-keyword">values</span>(<span class="hljs-string">'好好好   '</span>);</span> <span class="hljs-comment">-- 后面有3个空格</span><br>Query OK, 1 row affected (0.02 sec)<br><br><span class="hljs-comment">-- </span><br><span class="hljs-comment">-- test_char 表</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a, length(a) <span class="hljs-keyword">from</span> test_char;</span>   <br>+<span class="hljs-comment">----------------+-----------+</span><br>| a              | length(a) |<br>+<span class="hljs-comment">----------------+-----------+</span><br>| abc            |         3 |<br>| 你好吗         |         9 |<br>| 大家好ab       |        11 |<br>| 大家ab好       |        11 |<br>| 大家ab好吗     |        14 |<br>| 好好好         |         9 |  <span class="hljs-comment">-- 只有9个字节</span><br>+<span class="hljs-comment">----------------+-----------+</span><br>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> a, <span class="hljs-keyword">hex</span>(a) <span class="hljs-keyword">from</span> test_char;</span><br>+<span class="hljs-comment">----------------+------------------------------+</span><br>| a              | hex(a)                       |<br>+<span class="hljs-comment">----------------+------------------------------+</span><br>| abc            | 616263                       |<br>| 你好吗         | E4BDA0E5A5BDE59097           |<br>| 大家好ab       | E5A4A7E5AEB6E5A5BD6162       |<br>| 大家ab好       | E5A4A7E5AEB66162E5A5BD       |<br>| 大家ab好吗     | E5A4A7E5AEB66162E5A5BDE59097 |<br>| 好好好         | E5A5BDE5A5BDE5A5BD           | <span class="hljs-comment">-- 无填充空格</span><br>+<span class="hljs-comment">----------------+------------------------------+</span><br>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- test_varchar表</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> a, length(a) <span class="hljs-keyword">from</span> test_varchar;</span><br>+<span class="hljs-comment">----------------+-----------+</span><br>| a              | length(a) |<br>+<span class="hljs-comment">----------------+-----------+</span><br>| abc            |         3 |<br>| 你好吗         |         9 |<br>| 大家好ab       |        11 |<br>| 大家ab好       |        11 |<br>| 大家ab好吗     |        14 |<br>| 好好好         |        12 |  <span class="hljs-comment">-- (好好好)9个字节 +  3个字节的空格</span><br>+<span class="hljs-comment">----------------+-----------+</span><br>7 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> a, <span class="hljs-keyword">hex</span>(a) <span class="hljs-keyword">from</span> test_varchar;</span>      <br>+<span class="hljs-comment">----------------+------------------------------+</span><br>| a              | hex(a)                       |<br>+<span class="hljs-comment">----------------+------------------------------+</span><br>| abc            | 616263                       |<br>| 你好吗         | E4BDA0E5A5BDE59097           |<br>| 大家好ab       | E5A4A7E5AEB6E5A5BD6162       |<br>| 大家ab好       | E5A4A7E5AEB66162E5A5BD       |<br>| 大家ab好吗     | E5A4A7E5AEB66162E5A5BDE59097 |<br>| 好好好         | E5A5BDE5A5BDE5A5BD202020     |  <span class="hljs-comment">-- 后面有20 20 20 ，表示3个自己的空格</span><br>+<span class="hljs-comment">----------------+------------------------------+</span><br>7 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p>上面的现象无法用统一的规则进行表述，但是<a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html" target="_blank">官方文档</a>给出的解释是，这样的安排是为了避免索引页的碎片 </p>
</blockquote>

</div><div id="wmd-preview-section-121" class="wmd-preview-section preview-content">

<h3 id="3blob和text">3.BLOB和TEXT</h3>

<ul><li>在BLOB和TEXT上创建索引时，必须指定索引前缀的长度</li>
</ul>

</div><div id="wmd-preview-section-122" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_text(a <span class="hljs-built_in">int</span> <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>, b <span class="hljs-built_in">text</span>, <span class="hljs-keyword">key</span>(b));</span><br>ERROR 1170 (42000): BLOB/TEXT column 'b' used in key specification without a key length<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_text(a <span class="hljs-built_in">int</span> <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>, b <span class="hljs-built_in">text</span>, <span class="hljs-keyword">key</span>(b(<span class="hljs-number">64</span>)));</span><br>Query OK, 0 rows affected (0.13 sec)<br></code></pre>

<ul><li>BLOB和TEXT列不能有默认值</li>
<li>BLOB和TEXT列排序时只使用该列的前max_sort_length个字节</li>
</ul>

</div><div id="wmd-preview-section-123" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @@max_sort_length;</span><br>+<span class="hljs-comment">-------------------+</span><br>| @@max_sort_length |<br>+<span class="hljs-comment">-------------------+</span><br>|              1024 |<br>+<span class="hljs-comment">-------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p>不建议在MySQL中存储大型的二进制数据，比如歌曲，视频</p>
</blockquote>

<hr>

</div><div id="wmd-preview-section-124" class="wmd-preview-section preview-content">

<h2 id="四-字符集">四. 字符集</h2>

</div><div id="wmd-preview-section-125" class="wmd-preview-section preview-content">

<h3 id="1-常见的字符集">1. 常见的字符集</h3>

<ul><li>utf8</li>
<li>utf8mb4</li>
<li>gbk</li>
<li>gb18030</li>
</ul>

</div><div id="wmd-preview-section-126" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-built_in">character</span> <span class="hljs-keyword">set</span>;</span><br>+<span class="hljs-comment">----------+---------------------------------+---------------------+--------+</span><br>| Charset  | Description                     | Default collation   | Maxlen |<br>+<span class="hljs-comment">----------+---------------------------------+---------------------+--------+</span><br>| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |<br>| dec8     | DEC West European               | dec8_swedish_ci     |      1 |<br>| cp850    | DOS West European               | cp850_general_ci    |      1 |<br>| hp8      | HP West European                | hp8_english_ci      |      1 |<br>| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |<br>| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |<br>| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |<br>| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |<br>| ascii    | US ASCII                        | ascii_general_ci    |      1 |<br>| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |<br>| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |<br>| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |<br>| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |<br>| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |<br>| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |<br>| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |<br>| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |<br>| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |<br>| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 | <span class="hljs-comment">-- gbk，表示的字符有限</span><br>| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |<br>| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |<br>| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 | <span class="hljs-comment">-- utf8，最长3字节</span><br>| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |<br>| cp866    | DOS Russian                     | cp866_general_ci    |      1 |<br>| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |<br>| macce    | Mac Central European            | macce_general_ci    |      1 |<br>| macroman | Mac West European               | macroman_general_ci |      1 |<br>| cp852    | DOS Central European            | cp852_general_ci    |      1 |<br>| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |<br>| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 | <span class="hljs-comment">-- utf8 + mobile端字符</span><br>| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |<br>| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |<br>| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |<br>| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |<br>| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |<br>| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |<br>| binary   | Binary pseudo charset           | binary              |      1 |<br>| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |<br>| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |<br>| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |<br>| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 | <span class="hljs-comment">-- gb18030,最长4个字节</span><br>+<span class="hljs-comment">----------+---------------------------------+---------------------+--------+</span><br>41 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-127" class="wmd-preview-section preview-content">

<h3 id="2-collation">2. collation</h3>

<p>collation的含义是指排序规则，<code>ci（case insensitive）</code>结尾的排序集是不区分大小写的</p>

</div><div id="wmd-preview-section-128" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-string">'a'</span> = <span class="hljs-string">'A'</span>;</span><br>+<span class="hljs-comment">-----------+</span><br>| 'a' = 'A' |<br>+<span class="hljs-comment">-----------+</span><br>|         1 |  <span class="hljs-comment">-- 因为大小写无关，所以返回1</span><br>+<span class="hljs-comment">-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_ci (a <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>), <span class="hljs-keyword">key</span>(a));</span><br>Query OK, 0 rows affected (0.13 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_ci <span class="hljs-keyword">values</span>(<span class="hljs-string">'a'</span>);</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_ci <span class="hljs-keyword">values</span>(<span class="hljs-string">'A'</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_ci <span class="hljs-keyword">where</span> a = <span class="hljs-string">'a'</span>;</span><br>+<span class="hljs-comment">------+</span><br>| a    |<br>+<span class="hljs-comment">------+</span><br>| a    |  <br>| A    |  <span class="hljs-comment">-- A也被我们查到了</span><br>+<span class="hljs-comment">------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p>上面的情况如果从业务的角度上看，可以很好理解，比如创建一个用户叫做Tom，你是不希望再创建一个叫做tom的用户的</p>
</blockquote>

<ul><li><strong>修改默认的collation</strong></li>
</ul>

</div><div id="wmd-preview-section-129" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">names</span> utf8mb4 <span class="hljs-keyword">collate</span> utf8mb4_bin;</span>  <span class="hljs-comment">-- 当前会话有效</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-string">'a'</span> = <span class="hljs-string">'A'</span>;</span><br>+<span class="hljs-comment">-----------+</span><br>| 'a' = 'A' |<br>+<span class="hljs-comment">-----------+</span><br>|         0 |<br>+<span class="hljs-comment">-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p>字符集的指定，可以在创建数据库的时候指定，也可以在创建表的时候单独指定，也可以创建列的时候进行指定</p>
</blockquote>

<hr>

</div><div id="wmd-preview-section-413" class="wmd-preview-section preview-content">

<h2 id="五-集合类型">五. 集合类型</h2>

<ul><li>集合类型ENUM 和 SET</li>
<li>ENUM类型最多允许65536个值</li>
<li>SET类型最多允许64个值</li>
<li>通过sql_mode参数可以用户约束检查</li>
</ul>

</div><div id="wmd-preview-section-562" class="wmd-preview-section preview-content">

<h3 id="1-集合类型的排序">1. 集合类型的排序</h3></div><div id="wmd-preview-section-306" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_col (<br>    -&gt; <span class="hljs-keyword">user</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>),<br>    -&gt; sex enum(<span class="hljs-string">'male'</span>, <span class="hljs-string">'female'</span>)  <span class="hljs-comment">-- 虽然写的是字符串，单其实存储的整型，效率还是可以的</span><br>    -&gt; );</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_col <span class="hljs-keyword">values</span> (<span class="hljs-string">"tom"</span>, <span class="hljs-string">"male"</span>);</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_col <span class="hljs-keyword">values</span> (<span class="hljs-string">"tom"</span>, <span class="hljs-string">"xmale"</span>);</span>  <span class="hljs-comment">-- 不是male 和 female</span><br>Query OK, 1 row affected, 1 warning (0.03 sec)  <span class="hljs-comment">-- 有warning</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">set</span> sql_mode=<span class="hljs-string">'strict_trans_tables'</span>;</span>  <span class="hljs-comment">-- 设置为严格模式</span><br>Query OK, 0 rows affected, 2 warnings (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_col <span class="hljs-keyword">values</span> (<span class="hljs-string">"tom"</span>, <span class="hljs-string">"xmale"</span>);</span><br>ERROR 1265 (01000): Data truncated for column 'sex' at row 1<br></code></pre>

<blockquote>
  <p>强烈建议新业务上都设置成严格模式</p>
</blockquote>

</div><div id="wmd-preview-section-823" class="wmd-preview-section preview-content">

<h3 id="2-集合类型的排序">2. 集合类型的排序</h3>

</div><div id="wmd-preview-section-8192" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_col_sort(<br>    -&gt; <span class="hljs-keyword">user</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">10</span>),<br>    -&gt; type enum(<span class="hljs-string">'aaa'</span>,<span class="hljs-string">'zzz'</span>,<span class="hljs-string">'bbb'</span>,<span class="hljs-string">'yyy'</span>,<span class="hljs-string">'fff'</span>)  <span class="hljs-comment">-- aaa=0, zzz=1, bbb=2, yyy=3, fff=4</span><br>    -&gt; );</span><br>Query OK, 0 rows affected (0.20 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_col_sort <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> type <span class="hljs-keyword">asc</span>;</span> <span class="hljs-comment">-- 以type作为key，进行升序排序</span><br>+<span class="hljs-comment">-------+------+</span><br>| user  | type |<br>+<span class="hljs-comment">-------+------+</span><br>| user1 | aaa  |  <span class="hljs-comment">-- 0</span><br>| user4 | zzz  |  <span class="hljs-comment">-- 1</span><br>| user2 | bbb  |  <span class="hljs-comment">-- 2</span><br>| user3 | yyy  |  <span class="hljs-comment">-- 3</span><br>+<span class="hljs-comment">-------+------+  -- 枚举类型实际是整型数据，按照插入顺序进行排列</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 使用ascii排序</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_col_sort <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">cast</span>(type <span class="hljs-keyword">as</span> <span class="hljs-built_in">char</span>) <span class="hljs-keyword">asc</span>;</span>  <span class="hljs-comment">-- 使用cast()函数转换成某种型</span><br>+<span class="hljs-comment">-------+------+                                                     -- 这里我们转成char型</span><br>| user  | type |                                                     <span class="hljs-comment">-- 然后进行排序(ascii)</span><br>+<span class="hljs-comment">-------+------+</span><br>| user1 | aaa  | <span class="hljs-comment">-- 0</span><br>| user2 | bbb  | <span class="hljs-comment">-- 2</span><br>| user3 | yyy  | <span class="hljs-comment">-- 3</span><br>| user4 | zzz  | <span class="hljs-comment">-- 1</span><br>+<span class="hljs-comment">-------+------+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">-- 或者使用concat</span><br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_col_sort <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">concat</span>(type) <span class="hljs-keyword">asc</span>;</span>   <span class="hljs-comment">-- concat()是连接字符串函数            </span><br>+<span class="hljs-comment">-------+------+</span><br>| user  | type |<br>+<span class="hljs-comment">-------+------+</span><br>| user1 | aaa  |  <span class="hljs-comment">-- 0</span><br>| user2 | bbb  |  <span class="hljs-comment">-- 2</span><br>| user3 | yyy  |  <span class="hljs-comment">-- 3</span><br>| user4 | zzz  |  <span class="hljs-comment">-- 1</span><br>+<span class="hljs-comment">-------+------+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">concat</span>(<span class="hljs-string">"abc"</span>, <span class="hljs-string">"大家好"</span>);</span><br>+<span class="hljs-comment">----------------------------+</span><br>| concat("abc", "大家好")    |<br>+<span class="hljs-comment">----------------------------+</span><br>| abc大家好                  |<br>+<span class="hljs-comment">----------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr></div><div id="wmd-preview-section-15382" class="wmd-preview-section preview-content">

<h2 id="六-日期类型">六. 日期类型</h2>

<table>
<thead>
<tr>
  <th>日期类型</th>
  <th>占用空间(byte)（&lt;5.6）</th>
  <th>占用空间(byte)(&gt;=5.6)</th>
  <th>表示范围</th>
</tr>
</thead>
<tbody><tr>
  <td><code>DATETIME</code></td>
  <td>8</td>
  <td>5 + 微秒存储空间</td>
  <td>1000-01-01 00:00:00 ~ 9999-12-31 23:59:59</td>
</tr>
<tr>
  <td>DATE</td>
  <td>3</td>
  <td>3</td>
  <td>1000-01-01 ~ 9999-12-31</td>
</tr>
<tr>
  <td><code>TIMESTAMP</code></td>
  <td>4</td>
  <td>4 + 微秒存储空间</td>
  <td>1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC</td>
</tr>
<tr>
  <td>YEAR</td>
  <td>1</td>
  <td>1</td>
  <td>YEAR(2):1970-2070, YEAR(4):1901-2155</td>
</tr>
<tr>
  <td>TIME</td>
  <td>3</td>
  <td>3 + 微秒存储空间</td>
  <td>-838:59:59 ~ 838:59:59</td>
</tr>
</tbody></table>


<table>
<thead>
<tr>
  <th>微秒位数</th>
  <th>所需存储空间</th>
</tr>
</thead>
<tbody><tr>
  <td>0</td>
  <td>0</td>
</tr>
<tr>
  <td>1, 2</td>
  <td>1 byte</td>
</tr>
<tr>
  <td>3, 4</td>
  <td>2 bytes</td>
</tr>
<tr>
  <td>5, 6</td>
  <td>3 bytes</td>
</tr>
</tbody></table>


<blockquote>
  <p>TIMESTAMP 带时区功能</p>
</blockquote></div><div id="wmd-preview-section-8660" class="wmd-preview-section preview-content">

<h3 id="1-timestamp和datetime">1. TIMESTAMP和DATETIME</h3></div><div id="wmd-preview-section-8739" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_time(a <span class="hljs-keyword">timestamp</span>, b datetime);</span><br>Query OK, 0 rows affected (0.12 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_time <span class="hljs-keyword">values</span> (<span class="hljs-keyword">now</span>(), <span class="hljs-keyword">now</span>());</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_time;</span><br>+<span class="hljs-comment">---------------------+---------------------+</span><br>| a                   | b                   |<br>+<span class="hljs-comment">---------------------+---------------------+</span><br>| 2015-11-28 10:00:39 | 2015-11-28 10:00:39 |<br>+<span class="hljs-comment">---------------------+---------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> @@time_zone;</span> <br>+<span class="hljs-comment">-------------+</span><br>| @@time_zone |<br>+<span class="hljs-comment">-------------+</span><br>| SYSTEM      |<br>+<span class="hljs-comment">-------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">set</span> time_zone=<span class="hljs-string">'+00:00'</span>;</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @@time_zone;</span><br>+<span class="hljs-comment">-------------+</span><br>| @@time_zone |<br>+<span class="hljs-comment">-------------+</span><br>| +00:00      |<br>+<span class="hljs-comment">-------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_time;</span><br>+<span class="hljs-comment">---------------------+---------------------+</span><br>| a                   | b                   |<br>+<span class="hljs-comment">---------------------+---------------------+</span><br>| 2015-11-28 2:00:39 | 2015-11-28 10:00:39  |  <span class="hljs-comment">-- 时区的差别体现出来了</span><br>+<span class="hljs-comment">---------------------+---------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-10260" class="wmd-preview-section preview-content">

<h3 id="2-微秒">2. 微秒</h3>

<p><strong>从<code>MySQL5.6.X</code>开始，支持<code>微秒</code>，最大显示<code>6位</code></strong></p>

</div><div id="wmd-preview-section-16657" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">now</span>(<span class="hljs-number">6</span>);</span><br>+<span class="hljs-comment">----------------------------+</span><br>| now(6)                     |<br>+<span class="hljs-comment">----------------------------+</span><br>| 2015-11-30 21:15:36.415358 |  <span class="hljs-comment">-- 6位 微秒显示</span><br>+<span class="hljs-comment">----------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">now</span>(<span class="hljs-number">7</span>);</span><br>ERROR 1426 (42000): Too-big precision 7 specified for 'now'. Maximum is 6.  <span class="hljs-comment">-- 不支持，最大到6</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_time_fac (t datetime(<span class="hljs-number">6</span>));</span><br>Query OK, 0 rows affected (0.11 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_time_fac <span class="hljs-keyword">values</span>(<span class="hljs-keyword">now</span>(<span class="hljs-number">6</span>));</span> <br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_time_fac;</span><br>+<span class="hljs-comment">----------------------------+</span><br>| t                          |<br>+<span class="hljs-comment">----------------------------+</span><br>| 2015-11-30 21:19:27.900393 |  <span class="hljs-comment">-- 由于是用了6位微秒位数，根据表格显示，实际存储的空间是 5 + 3 = 8 byte</span><br>+<span class="hljs-comment">----------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-28476" class="wmd-preview-section preview-content">

<h3 id="3-时间函数">3. 时间函数</h3>

<ul><li>常用函数</li>
</ul>

<table>
<thead>
<tr>
  <th>函数名</th>
  <th>函数说明</th>
  <th>备注</th>
</tr>
</thead>
<tbody><tr>
  <td>NOW</td>
  <td>返回<code>SQL执行时</code>的时间</td>
  <td>如果不考虑其他因素，可以理解为写完SQL，敲下回车瞬间的时间</td>
</tr>
<tr>
  <td>CURRENT_TIMESTAMP</td>
  <td>与NOW()函数同义</td>
  <td></td>
</tr>
<tr>
  <td>SYSDATE</td>
  <td>返回<code>函数执行时</code>的时间</td>
  <td>MySQL处理你的函数时的时间，统一SQL语句中，大于NOW</td>
</tr>
<tr>
  <td>DATA_ADD(date, interval expr uint)</td>
  <td>增加时间</td>
  <td></td>
</tr>
<tr>
  <td>DATA_SUB(date, interval expr uint)</td>
  <td>减少时间</td>
  <td>可用ADD，然后unit给负数</td>
</tr>
<tr>
  <td>DATE FORMAT</td>
  <td>格式化时间</td>
  <td></td>
</tr>
</tbody></table>


<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html" target="_blank">所有时间函数–官方文档</a></p>
</blockquote></div><div id="wmd-preview-section-28907" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- NOW和SYSDATE的区别</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">now</span>(<span class="hljs-number">6</span>),<span class="hljs-keyword">sysdate</span>(<span class="hljs-number">6</span>),<span class="hljs-keyword">sleep</span>(<span class="hljs-number">5</span>),<span class="hljs-keyword">now</span>(<span class="hljs-number">6</span>),<span class="hljs-keyword">sysdate</span>(<span class="hljs-number">6</span>);</span><br>+<span class="hljs-comment">----------------------------+----------------------------+----------+----------------------------+----------------------------+</span><br>| now(6)                     | sysdate(6)                 | sleep(5) | now(6)                     | sysdate(6)                 |<br>+<span class="hljs-comment">----------------------------+----------------------------+----------+----------------------------+----------------------------+</span><br>| 2015-11-30 21:40:58.572383 | 2015-11-30 21:40:58.572542 |        0 | 2015-11-30 21:40:58.572383 | 2015-11-30 21:41:03.572720 |<br>+<span class="hljs-comment">----------------------------+----------------------------+----------+----------------------------+----------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">5.00</span> sec)<br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 两个now(6)都相等，因为是SQL执行时的时间(可以简单立理解为按回车的时间)</span><br><span class="hljs-comment">-- 两个sysdate(6)差了5秒，刚好是sleep(5)的时间</span><br><span class="hljs-comment">--</span><br><br><span class="hljs-comment">-----</span><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- date_add</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">date_add</span>(<span class="hljs-keyword">now</span>(), <span class="hljs-built_in">interval</span> <span class="hljs-number">5</span> <span class="hljs-keyword">day</span>);</span>   <span class="hljs-comment">-- 增加5天</span><br>+<span class="hljs-comment">---------------------------------+</span><br>| date_add(now(), interval 5 day) |<br>+<span class="hljs-comment">---------------------------------+</span><br>| 2015-12-05 21:42:39             |<br>+<span class="hljs-comment">---------------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">date_add</span>(<span class="hljs-keyword">now</span>(), <span class="hljs-built_in">interval</span> -<span class="hljs-number">5</span> <span class="hljs-keyword">month</span>);</span>  <span class="hljs-comment">-- 减少 5个月</span><br>+<span class="hljs-comment">------------------------------------+</span><br>| date_add(now(), interval -5 month) |<br>+<span class="hljs-comment">------------------------------------+</span><br>| 2015-06-30 21:43:49                |<br>+<span class="hljs-comment">------------------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">date_sub</span>(<span class="hljs-keyword">now</span>(), <span class="hljs-built_in">interval</span> <span class="hljs-number">5</span> <span class="hljs-keyword">month</span>);</span>  <span class="hljs-comment">-- 与add + 负数一致</span><br>+<span class="hljs-comment">-----------------------------------+</span><br>| date_sub(now(), interval 5 month) |<br>+<span class="hljs-comment">-----------------------------------+</span><br>| 2015-06-30 21:44:21               |<br>+<span class="hljs-comment">-----------------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- date_format</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DATE_FORMAT</span>((<span class="hljs-keyword">select</span> <span class="hljs-keyword">now</span>(<span class="hljs-number">6</span>)), <span class="hljs-string">'%H:%i:%s'</span>);</span><br>+<span class="hljs-comment">------------------------------------------+</span><br>| DATE_FORMAT((<span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">now</span>(<span class="hljs-number">6</span>)), <span class="hljs-string">'%H:%i:%s'</span>) |<br>+<span class="hljs-comment">------------------------------------------+</span><br>| <span class="hljs-number">21</span>:<span class="hljs-number">48</span>:<span class="hljs-number">30</span>                                 |<br>+<span class="hljs-comment">------------------------------------------+</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-29832" class="wmd-preview-section preview-content">

<h3 id="4-字段更新时间">4. 字段更新时间</h3>

</div><div id="wmd-preview-section-33973" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span>  <span class="hljs-keyword">table</span> test_field_update(<br>    -&gt; a <span class="hljs-built_in">int</span>(<span class="hljs-number">10</span>),<br>    -&gt; b <span class="hljs-keyword">timestamp</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span> <span class="hljs-keyword">default</span> <span class="hljs-keyword">current_timestamp</span> <span class="hljs-keyword">on</span> <span class="hljs-keyword">update</span> <span class="hljs-keyword">current_timestamp</span><br>    -&gt; );</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_field_update <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>, <span class="hljs-keyword">now</span>(<span class="hljs-number">6</span>));</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_field_update;</span><br>+<span class="hljs-comment">------+---------------------+</span><br>| a    | b                   |<br>+<span class="hljs-comment">------+---------------------+</span><br>|    1 | 2015-11-30 21:55:18 |  <span class="hljs-comment">-- 上面使用了now(6)，但是这里没有微秒，是因为定义的时候就是timestamp</span><br>+<span class="hljs-comment">------+---------------------+  -- 如果写成timestamp(6),就可以显示微秒</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">update</span> test_field_update <span class="hljs-keyword">set</span> a=<span class="hljs-number">100</span> <span class="hljs-keyword">where</span> a=<span class="hljs-number">1</span>;</span>  <span class="hljs-comment">-- 只更新a字段</span><br>Query OK, 1 row affected (0.03 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_field_update;</span>    <br>+<span class="hljs-comment">------+---------------------+</span><br>| a    | b                   |<br>+<span class="hljs-comment">------+---------------------+</span><br>|  100 | 2015-11-30 22:01:03 |  <span class="hljs-comment">-- 发现b字段跟着改变了</span><br>+<span class="hljs-comment">------+---------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 测试timestamp(6)</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_time_disp(<br>    -&gt; a <span class="hljs-built_in">int</span>(<span class="hljs-number">10</span>),<br>    -&gt; b <span class="hljs-keyword">timestamp</span>(<span class="hljs-number">6</span>) <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span> <span class="hljs-keyword">default</span> <span class="hljs-keyword">current_timestamp</span>(<span class="hljs-number">6</span>) <span class="hljs-keyword">on</span> <span class="hljs-keyword">update</span> <span class="hljs-keyword">current_timestamp</span>(<span class="hljs-number">6</span>)  <span class="hljs-comment">-- 定义了(6)</span><br>    -&gt; );</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_time_disp <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>, <span class="hljs-keyword">now</span>(<span class="hljs-number">6</span>));</span>      <br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_time_disp;</span><br>+<span class="hljs-comment">------+----------------------------+</span><br>| a    | b                          |<br>+<span class="hljs-comment">------+----------------------------+</span><br>|    1 | 2015-11-30 22:03:23.545406 |  <span class="hljs-comment">-- 插入了now(6), 这里就显示了6位微秒</span><br>+<span class="hljs-comment">------+----------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br></span><br></code></pre></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>